package database.manipulation;
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import com.mysql.jdbc.NonRegisteringDriver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author eslam
 */
public class DataBaseManipulation {

    /**
     * @param args the command line arguments
     */
    
// java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
 public java.sql.Statement connect(String user, String pass, String database, String host)
 {
     java.sql.Statement statement = null;
        try {
            java.sql.Connection connection;
            DriverManager.registerDriver(new NonRegisteringDriver());
            connection = DriverManager.getConnection("jdbc:mysql://"+host+":3306/"+database, user, pass);
            statement = connection.createStatement();            
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
        return statement;
 }
 
 
 public void blockContact(String me, String name)
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
        try {
            int myId = getUserIdByName(me);
            int userId = getUserIdByName(name);
            String queryString = "update contact_list set status = blocked where user_id = "+myId+" and contact_user_id = "+userId; 
            int result = statement.executeUpdate(queryString);
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
 }
 
 
public void activateContact(String me, String name)
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
        try {
            int myId = getUserIdByName(me);
            int userId = getUserIdByName(name);
            String queryString = "update contact_list set status = active where user_id = "+myId+" and contact_user_id = "+userId; 
            int result = statement.executeUpdate(queryString);
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
 }
 public int delContact(String me , String name)
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");
     int confirmation = 0;
     int uid = getUserIdByName(me);
     int cid = getUserIdByName(name);
     if( areWeFriends(me,name) == 0)
     {
            try {
                String queryStatement = new String("delete from contact_list where where user_id ="+uid+"and contact_user_id = "+cid);
                int result = statement.executeUpdate(queryStatement);
                confirmation=1;
            } catch (SQLException ex) {
                Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
            }
     }
     return confirmation;
 }

 
 public void deleteUser(String user)
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
        try {
            String queryString = "delete from user where user_name ="+user;
            int result = statement.executeUpdate(queryString);
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
 }
 
 public int validateLogin(String name, String pass)
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
        int authenticated = 0;
        try {
            String queryStatement =new String("select user_name , password from user where user_name ="+name+"and password = "+pass);
            ResultSet result = statement.executeQuery(queryStatement);
            
            if(result.next())
            {
                authenticated =1;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
        return authenticated;
 }
 
  public int amIUnique(String name)
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
        int unique= 1;
        try {
            String queryStatement =new String("select user_name from user where user_name ="+"\""+name+"\"");
            ResultSet result = statement.executeQuery(queryStatement);
            
            if(result.next())
            {
                unique=0;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
        return unique;
 } 
 
 
 
 public int addUser(String name ,String pass , String DOB , String answer , String mail , String altMail , String status)
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
     int confirmation = 0;
     
     if( amIUnique(name) == 1)
     {
            try {
                String queryStatement = new String("insert into user (user_name,password,dob,answer,e_mail,alternative_e_mail,status) values (\""+name+"\",\""+pass+"\",\""+DOB+"\",\""+answer+"\",\""+mail+"\",\""+altMail+"\",\""+status+"\")");
                int result = statement.executeUpdate(queryStatement);
                confirmation=1;
            } catch (SQLException ex) {
                Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
            }
     }
     return confirmation;
 }
 
 public int getUserIdByName (String name)
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
      int userId = 0;
        try {
           
            
            String queryStatement =new String("select id from user where user_name ="+name); 
            ResultSet result = statement.executeQuery(queryStatement);
            if(result.next())
            {
               userId = Integer.parseInt(result.getString("id"));
            }
          
            
            
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return userId;
        }
 }
 
 
 public String getUserNameById (int id)
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
      String name = null;
        try {
           
            
            String queryStatement =new String("select user_name from user where id ="+id); 
            ResultSet result = statement.executeQuery(queryStatement);
            if(result.next())
            {
               name = result.getString("user_name");
            }
          
            
            
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            return name;
        }
 }
 
 
 public int areWeFriends(String me,String name)
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
     int friends = 0;
     int uid = getUserIdByName(me);
     int cid = getUserIdByName(name);
     String queryStatement =new String("select * from contact_list where user_id ="+uid+"and contact_user_id = "+cid);
        try {
            ResultSet result = statement.executeQuery(queryStatement);
            if(result.next())
            {
                friends=1;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
     
     return friends;
 }
 
  public int addContact(String me , String name)
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
     int confirmation = 0;
     
     if( areWeFriends(me,name) == 0)
     {
            try {
                String queryStatement = new String("insert into contact_list values ("+me+","+name+",waiting)");
                int result = statement.executeUpdate(queryStatement);
                confirmation=1;
            } catch (SQLException ex) {
                Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
            }
     }
     return confirmation;
 }
 
 
 
 public void changeUserStatus(String user, String status)       
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
        try {
            String queryString = "update user set status = "+status+"where user_name = "+user;
            int result = statement.executeUpdate(queryString);
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
 }
 
 
public String getUserStatus(String user)
{
    java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
        String status = null;
        try {
            String queryString = "select status from user where user_name ="+user;
            ResultSet result = statement.executeQuery(queryString);
            result.next();
            status = result.getString("status");
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
        return status;
}

public String[] getContactsStatus(String user)
{
    java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
    String[] status=null;
        try {
            int i=0;
            int userId = getUserIdByName(user);
            String queryString = "select u.status from user u , contact_list c where u.id = c.contact_user_id and c.user_id = "+userId;
            ResultSet result = statement.executeQuery(queryString);
            
            while(result.next())
            {
                status[i] = result.getString("status");
                i++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
        return status;
}

 
 
 public String[] getContacts(String user)     
 {
     java.sql.Statement statement = connect("root", "3ngm4ry", "JavaMessenger", "localhost");    
     int[] contactIds = null;
     String[] contacts =null;
     int userId,i=0;
        try //prototype
        {
            userId = getUserIdByName(user);
            String queryStrig = "select contact_user_id from contact_list where id = "+userId;
            ResultSet result = statement.executeQuery(queryStrig);
            
            while(result.next())
            {
                contactIds[i]=Integer.parseInt(result.getString("contact_user_id"));
                contacts[i] =getUserNameById(contactIds[i]);
                i++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManipulation.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
        return contacts;
 }
 
    public static void main(String[] args) {
        // TODO code application logic here
        
        DataBaseManipulation a = new DataBaseManipulation();
        a.addUser("test", "test2", "test3", "test4", "test5", "test6", "test7");
    }
}
